/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package workforce.limousine.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import limousine.vo.Customer;

/**
 *
 * @author krisada
 */
public class CustomerService implements Service {

    private int limit = 20;

    public int getLimit() {
        return limit;
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }
    public DataSource dataSource = null;

    public boolean save(Object obj) {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            Customer o = (Customer) obj;
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO customer (CUSTOMER_ID,CUSTOMER_TYPE,");
            sql.append("CUSTOMER_NAME,BILL_NAME,CUSTOMER_ADDRESS,");
            sql.append("BILL_ADDRESS,CUSTOMER_TEL,BILL_TEL,");
            sql.append("CUSTOMER_EMAIL,CREATE_BY,");
            sql.append("CREATE_DATE,UPDATE_BY,UPDATE_DATE)");
            sql.append("VALUES(?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP,?,CURRENT_TIMESTAMP);");
            PreparedStatement ps = connection.prepareStatement(sql.toString());
            ps.setString(1, o.getCustomerId());
            ps.setInt(2, o.getCustomerType());
            ps.setString(3, o.getCustomerName());
            ps.setString(4, o.getBillName());
            ps.setString(5, o.getCustomerAddress());
            ps.setString(6, o.getBillAddress());
            ps.setString(7, o.getCustomerTel());
            ps.setString(8, o.getBillTel());            
            ps.setString(9, o.getCustomerEmail());
            ps.setString(10, o.getCreateBy());
            ps.setString(11, o.getUpdateBy());            
            ps.executeUpdate();
            ps.close();
            connection.close();
            return true;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                    connection = null;
                } catch (Exception e) {
                }
            }
        }
        return false;
    }

    public int searchCount(int offset, Object[] certirial) {
        int count = 0;
        StringBuilder s = new StringBuilder();
        s.append("SELECT COUNT(*) FROM customer");
        if (certirial != null) {
            if (certirial.length > 0) {
                s.append(" WHERE ");
            }
            if (certirial[0] != null) {
                s.append(" CUSTOMER_NAME like ?");
            }
            if (certirial[1] != null) {
                if (certirial[0] != null) {
                    s.append(" AND ");
                }
                s.append(" BILL_NAME like ?");
            }

        }
        Connection conn = null;
        try {
            int cnt = 1;
            conn = dataSource.getConnection();
            PreparedStatement p = conn.prepareStatement(s.toString());
            if (certirial != null) {

                if (certirial[0] != null) {
                    String pns = (String) certirial[0];
                    pns = (pns != null) ? pns.replace('*', '%') : "";
                    p.setString(cnt, pns);
                    cnt++;
                }
                if (certirial[1] != null) {
                    String pns = (String) certirial[1];
                    pns = (pns != null) ? pns.replace('*', '%') : "";
                    p.setString(cnt, pns);
                    cnt++;
                }

            }
            ResultSet rs = p.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
            rs.close();
            p.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception e) {
                }
            }
        }
        return count;
    }

    public boolean update(Object obj) {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            Customer customer = (Customer) obj;
            StringBuilder sql = new StringBuilder();
            sql.append("UPDATE customer set CUSTOMER_TYPE = ?,");
            sql.append("CUSTOMER_NAME=?,BILL_NAME=?,CUSTOMER_ADDRESS=?,");
            sql.append("BILL_ADDRESS=?,CUSTOMER_TEL=?,BILL_TEL=?,");
            sql.append("CUSTOMER_EMAIL=?,");
            sql.append("UPDATE_BY=?,UPDATE_DATE=CURRENT_TIMESTAMP WHERE CUSTOMER_ID = ?");
            PreparedStatement ps = connection.prepareStatement(sql.toString());
            ps.setInt(1, customer.getCustomerType());
            ps.setString(2, customer.getCustomerName());
            ps.setString(3, customer.getBillName());
            ps.setString(4, customer.getCustomerAddress());
            ps.setString(5, customer.getBillAddress());
            ps.setString(6, customer.getCustomerTel());
            ps.setString(7, customer.getBillTel());            
            ps.setString(8, customer.getCustomerEmail());
            ps.setString(9, customer.getUpdateBy());
            ps.setString(10, customer.getCustomerId());
            ps.executeUpdate();
            ps.close();
            connection.close();
            return true;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                    connection = null;
                } catch (Exception e) {
                }
            }
        }
        return false;
    }

    public boolean remove(Object obj) {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            String customerId = (String) obj;
            StringBuilder sql = new StringBuilder();
            sql.append("DELETE FROM customer WHERE CUSTOMER_ID = ?");
            PreparedStatement ps = connection.prepareStatement(sql.toString());
            ps.setString(1, customerId);
            ps.executeUpdate();
            ps.close();
            connection.close();
            return true;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                    connection = null;
                } catch (Exception e) {
                }
            }
        }
        return false;
    }

    public Object findById(String id) {
        StringBuilder s = new StringBuilder();
        s.append("SELECT ");
        s.append("IFNULL(A.CUSTOMER_ID, '-') AS CUSTOMER_ID,");
        s.append("IFNULL(A.CUSTOMER_TYPE, 0) AS CUSTOMER_TYPE,"); 
        s.append("IFNULL(A.CUSTOMER_NAME, '-') AS CUSTOMER_NAME,");
        s.append("IFNULL(A.BILL_NAME, '-') AS BILL_NAME,");
        s.append("IFNULL(A.CUSTOMER_ADDRESS, '-') AS CUSTOMER_ADDRESS,");
        s.append("IFNULL(A.BILL_ADDRESS, '-') AS BILL_ADDRESS,");
        s.append("IFNULL(A.CUSTOMER_TEL, '-') AS CUSTOMER_TEL,");
        s.append("IFNULL(A.BILL_TEL, '-') AS BILL_TEL,");
        s.append("IFNULL(A.CUSTOMER_EMAIL, '-') AS CUSTOMER_EMAIL,");
        s.append("IFNULL(A.CREATE_BY, '-') AS CREATE_BY,");
        s.append("IFNULL(A.CREATE_DATE, CURRENT_TIMESTAMP) AS CREATE_DATE,");
        s.append("IFNULL(A.UPDATE_BY, '-') AS UPDATE_BY,");
        s.append("IFNULL(A.UPDATE_DATE, CURRENT_TIMESTAMP) AS UPDATE_DATE ");
        s.append("FROM customer A WHERE A.CUSTOMER_ID = ?");
        Connection conn = null;
        Customer customer = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement p = conn.prepareStatement(s.toString());
            p.setString(1, id);
            ResultSet rs = p.executeQuery();
            if (rs.next()) {
                customer = new Customer();
                customer.setCustomerId(rs.getString(1));
                customer.setCustomerType(rs.getInt(2));
                customer.setCustomerName(rs.getString(3));
                customer.setBillName(rs.getString(4));
                customer.setCustomerAddress(rs.getString(5));
                customer.setBillAddress(rs.getString(6));
                customer.setCustomerTel(rs.getString(7));
                customer.setBillTel(rs.getString(8));                
                customer.setCustomerEmail(rs.getString(9));
                customer.setCreateBy(rs.getString(10));
                customer.setCreateDate(new Date(rs.getTimestamp(11).getTime()));
                customer.setUpdateBy(rs.getString(12));
                customer.setUpdateDate(new Date(rs.getTimestamp(13).getTime()));
            }
            rs.close();
            p.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception e) {
                }
            }
        }
        return customer;
    }

    public List find(Object[] obj) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
    public List<Customer> search(int offset, Object[] certiria) {
        System.out.println("CarService search...");
        List<Customer> list = new ArrayList<Customer>();
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            StringBuilder s = new StringBuilder();
            s.append("SELECT ");
            s.append("IFNULL(A.CUSTOMER_ID, '-') AS CUSTOMER_ID,");
            s.append("IFNULL(A.CUSTOMER_TYPE, 0) AS CUSTOMER_TYPE,");
            s.append("(SELECT IFNULL(B.LOOKUP_NAME,'-') FROM lookup B WHERE B.LOOKUP_VALUE = A.CUSTOMER_TYPE AND B.LOOKUP_GROUP_ID = 'CUSTOMER_TYPE' ) AS CUSTOMER_TYPE_NAME,");
            s.append("IFNULL(A.CUSTOMER_NAME, '-') AS CUSTOMER_NAME,");
            s.append("IFNULL(A.BILL_NAME, '-') AS BILL_NAME,");
            s.append("IFNULL(A.CUSTOMER_ADDRESS, '-') AS CUSTOMER_ADDRESS,");
            s.append("IFNULL(A.BILL_ADDRESS, '-') AS BILL_ADDRESS,");
            s.append("IFNULL(A.CUSTOMER_TEL, '-') AS CUSTOMER_TEL,");
            s.append("IFNULL(A.BILL_TEL, '-') AS BILL_TEL,");
            s.append("IFNULL(A.CUSTOMER_EMAIL, '-') AS CUSTOMER_EMAIL,");
            s.append("IFNULL(A.CREATE_BY, '-') AS CREATE_BY,");
            s.append("IFNULL(A.CREATE_DATE, CURRENT_TIMESTAMP) AS CREATE_DATE,");
            s.append("IFNULL(A.UPDATE_BY, '-') AS UPDATE_BY,");
            s.append("IFNULL(A.UPDATE_DATE, CURRENT_TIMESTAMP) AS UPDATE_DATE ");
            s.append("FROM customer A WHERE A.CUSTOMER_NAME LIKE ? ");
            s.append("LIMIT ? OFFSET ?");
            System.out.println("sql[" + s.toString() + "]");
            PreparedStatement stmt = conn.prepareStatement(s.toString());
            String name = (String) certiria[0];
            name = (name != null) ? name.replace('*', '%') : "";
            if(name.indexOf("%")==-1){
                name = "%"+name+"%";
            }
            stmt.setString(1, name);
            stmt.setInt(2, limit);
            stmt.setInt(3, offset);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Customer o = new Customer();
                o.setCustomerId(rs.getString("CUSTOMER_ID"));
                o.setCustomerType(rs.getInt("CUSTOMER_TYPE"));
                o.setCustomerTypeName(rs.getString("CUSTOMER_TYPE_NAME"));
                o.setCustomerName(rs.getString("CUSTOMER_NAME"));
                o.setBillName(rs.getString("BILL_NAME"));
                o.setCustomerAddress(rs.getString("CUSTOMER_ADDRESS"));
                o.setBillAddress(rs.getString("BILL_ADDRESS"));
                o.setCustomerTel(rs.getString("CUSTOMER_TEL"));
                o.setBillTel(rs.getString("BILL_TEL"));
                o.setCustomerEmail(rs.getString("CUSTOMER_EMAIL"));                                                     
                o.setCreateBy(rs.getString("CREATE_BY"));
                o.setCreateDate(new Date(rs.getDate("CREATE_DATE").getTime()));
                //o.setCreateDate(new Date());
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setUpdateDate(new Date(rs.getDate("UPDATE_DATE").getTime()));
//                o.setUpdateDate(new Date());
                list.add(o);
            }
            rs.close();
            rs = null;
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                }
            }
        }
        return list;
    }
    public boolean removeAll(String[] ids) {
        return false;
    }

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    public Object findById(String uid, String id) {
        StringBuilder s = new StringBuilder();
        s.append("SELECT ");
        s.append("IFNULL(A.CUSTOMER_ID, '-') AS CUSTOMER_ID,");
        s.append("IFNULL(A.CUSTOMER_TYPE, 0) AS CUSTOMER_TYPE,"); 
        s.append("IFNULL(A.CUSTOMER_NAME, '-') AS CUSTOMER_NAME,");
        s.append("IFNULL(A.BILL_NAME, '-') AS BILL_NAME,");
        s.append("IFNULL(A.CUSTOMER_ADDRESS, '-') AS CUSTOMER_ADDRESS,");
        s.append("IFNULL(A.BILL_ADDRESS, '-') AS BILL_ADDRESS,");
        s.append("IFNULL(A.CUSTOMER_TEL, '-') AS CUSTOMER_TEL,");
        s.append("IFNULL(A.BILL_TEL, '-') AS BILL_TEL,");
        s.append("IFNULL(A.CUSTOMER_EMAIL, '-') AS CUSTOMER_EMAIL,");
        s.append("IFNULL(A.CREATE_BY, '-') AS CREATE_BY,");
        s.append("IFNULL(A.CREATE_DATE, CURRENT_TIMESTAMP) AS CREATE_DATE,");
        s.append("IFNULL(A.UPDATE_BY, '-') AS UPDATE_BY,");
        s.append("IFNULL(A.UPDATE_DATE, CURRENT_TIMESTAMP) AS UPDATE_DATE ");
        s.append("FROM customer A WHERE A.CUSTOMER_ID = ?");
        Connection conn = null;
        Customer customer = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement p = conn.prepareStatement(s.toString());
            p.setString(1, id);
            ResultSet rs = p.executeQuery();
            if (rs.next()) {
                customer = new Customer();
                customer.setCustomerId(rs.getString(1));
                customer.setCustomerType(rs.getInt(2));
                customer.setCustomerName(rs.getString(3));
                customer.setBillName(rs.getString(4));
                customer.setCustomerAddress(rs.getString(5));
                customer.setBillAddress(rs.getString(6));
                customer.setCustomerTel(rs.getString(7));
                customer.setBillTel(rs.getString(8));                
                customer.setCustomerEmail(rs.getString(9));
                customer.setCreateBy(rs.getString(10));
                customer.setCreateDate(new Date(rs.getTimestamp(11).getTime()));
                customer.setUpdateBy(rs.getString(12));
                customer.setUpdateDate(new Date(rs.getTimestamp(13).getTime()));
            }
            rs.close();
            p.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception e) {
                }
            }
        }
        return customer;
    }    
}
